﻿using Model;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    /// <summary>
    /// 社团数据访问类,继承基类BaseDAL
    /// </summary>
    public class ClubDAL : BaseDAL<Club>
    {
        #region 查询

        /// <summary>
        /// 获取所有有效的社团
        /// </summary>
        /// <returns>社团数据表</returns>
        public DataTable GetAllClubs()
        {
            string sql =
                "SELECT Id,ClubName,FoundDate,Introduction,TeacherName,DeleteFlag " +
                "FROM Club " +
                "WHERE DeleteFlag = 0 " +
                "ORDER BY Id";
            return Query(sql);
        }

        /// <summary>
        /// 获取所有有效社团实体集合
        /// </summary>
        /// <returns>社团实体集合</returns>
        public List<Club> GetAllClubList()
        {
            DataTable dt = GetAllClubs();
            List<Club> clubs = new();
            foreach (DataRow dr in dt.Rows)
            {
                Club club = ConverToModel(dr);
                clubs.Add(club);
            }
            return clubs;
        }
        /// <summary>
        /// 根据Id查找Club
        /// </summary>
        /// <param name="id">Club的Id</param>
        /// <returns>一个Club实体或者null</returns>
        public Club? GetClubById(int id)
        {
            //定义查询语句
            string sql =
                "SELECT Id,ClubName,FoundDate,Introduction,TeacherName,DeleteFlag " +
                "FROM Club " +
                "WHERE Id = @Id AND DeleteFlag = 0 ";
            //定义参数
            MySqlParameter p = new MySqlParameter("@Id", MySqlDbType.Int32) { Value = id };
            //执行查询并获取查询结果：数据表
            DataTable dt = Query(sql, p);
            //判断表中是否有数据，若有就把数据表的第一行转换为ClubUser实体并返回这个实体，若没有就返回null
            return dt.Rows.Count > 0 ? ConverToModel(dt.Rows[0]) : null;

        }
        #endregion

        #region 增改删
        /// <summary>
        /// 添加社团
        /// </summary>
        /// <param name="club"></param>
        /// <returns>返回受影响的行数，添加成功返回1</returns>
        public int AddClub(Club club)
        {
            //定义查询语句
            string sql =
                "INSERT INTO Club(ClubName,FoundDate,Introduction,TeacherName) " +
                "VALUES(@ClubName,@FoundDate,@Introduction,@TeacherName";
            MySqlParameter[] ps =
            [
                new("@ClubName", MySqlDbType.VarChar, 200) {Value = club.ClubName },
                new("@FoundDate",MySqlDbType.DateTime) {Value = club.FoundDate},
                new("@Introduction",MySqlDbType.Text){Value = club.Introduction },
                new("@TeacherName",MySqlDbType.VarChar,50){Value=club.TeacherName}
            ];
            return Add(sql, ps);
        }
        /// <summary>
        /// 更新社团信息
        /// </summary>
        /// <param name="club"></param>
        /// <returns>返回受影响的行数，更新成功返回1</returns>
        public int UpdateClub(Club club)
        {
            string sql =
                "UPDATE Club " +
                "SET ClubName = @ClubName,FoundDate = @FoundDate,Introduction = @Introduction,TeacherName = @TeacherName " +
                "WHERE Id =@Id";
            MySqlParameter[] ps =
            [
                new("@ClubName", MySqlDbType.VarChar, 200) {Value = club.ClubName },
                new("@FoundDate",MySqlDbType.DateTime) {Value = club.FoundDate},
                new("@Introduction",MySqlDbType.Text){Value = club.Introduction },
                new("@TeacherName",MySqlDbType.VarChar,50){Value=club.TeacherName},
                new("@Id",MySqlDbType.Int32){Value = club.Id}
            ];
            return Update(sql, ps);
        }
        /// <summary>
        /// 逻辑删除一个社团，更新DeleteFlag字段值为1
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteClub(int id)
        {
            string sql =
                "UPDATE Club SET DeleteFlag = 1 WHERE Id = @Id";
            MySqlParameter p = new("@Id", MySqlDbType.Int32) { Value = id };
            return Update(sql, p);
        }
        #endregion

        #region 实体转换
        /// <summary>
        /// 把Club数据表中的行转换为Club实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns>CLub实体</returns>
        public override Club ConverToModel(DataRow dr)
        {
            return new Club
            {
                Id = (int)dr["Id"],
                ClubName = dr["ClubName"].ToString(),
                FoundDate = (DateTime)dr["FoundDate"],
                Introduction = dr["Introduction"].ToString(),
                TeacherName = dr["TeacherName"].ToString(),
                //把mysql数据库中tinyint类型字段转换为c#的bool类型
                DeleteFlag = Convert.ToBoolean( dr["DeleteFlag"])
            };
        }
        #endregion
    }
}
